<?php
/**
 * +----------------------------------------------------------------------
 * | 导出服务
 * +----------------------------------------------------------------------
 *                      _ooOoo_
 *                     o8888888o            | AUTHOR: 杜云
 *                     88" . "88            | EMAIL: 987772927@qq.com
 *                     (| -_- |)            | QQ: 987772927
 *                     O\  =  /O            | WECHAT: 987772927
 *                  ____/`---'\____
 *                .'  \\|     |//  `.
 *               /  \\|||  :  |||//  \
 *              /  _||||| -:- |||||-  \
 *              |   | \\\  -  /// |   |
 *              | \_|  ''\-/''  |   |
 *              \  .-\__  `-`  ___/-. /
 *            ___`. .'  /-.-\  `. . __
 *         ."" '<  `.___\_<|>_/___.'  >'"".
 *        | | :  `- \`.;`\ _ /`;.`/ - ` : | |
 *        \  \ `-.   \_ __\ /__ _/   .-` /  /
 *   ======`-.____`-.___\_____/___.-`____.-'======
 *                      `=-='
 * +----------------------------------------------------------------------
 */
namespace App\Service\Excel;

use Hyperf\HttpMessage\Stream\SwooleStream;
use Hyperf\HttpServer\Response;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\NamedRange;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use App\Constants\StatusCode;
/**
 * 函数说明：
 * 构造函数 创建一个PhpSpreadsheet实例 __construct（）
 * 设置表头 $title=['id','标题','内容'] setHeader($title)
 * 添加表内容$data=[ [1,标题1，内容1], [2,标题2，内容2], ... ] addData($data)
 * 保存到服务器本地 $fileName=文件名 saveToLocal($fileName)
 * 直接从浏览器下载到本地，有问题，不使用，php://output 目前PhpSpreadsheet插件有问题,PhpSpreadsheet插件作者还在修复saveToBrowser($fileName)
 * 保存临时文件在从浏览器自动下载到本地 saveToBrowserByTmp($fileName)
 */
class ExcelPortService
{
    private $sheet;
    private $spreadsheet;
    private $row;

    //构造函数 创建一个PhpSpreadsheet实例
    public function __construct()
    {
        // Create new Spreadsheet object
        $this->spreadsheet = new Spreadsheet();
        // Set document properties
        $this->spreadsheet->getProperties()->setCreator('Maarten Balliauw')
        ->setLastModifiedBy('Maarten Balliauw')
        ->setTitle('Office 2007 XLSX Test Document')
        ->setSubject('Office 2007 XLSX Test Document')
        ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
        ->setKeywords('office 2007 openxml php')
        ->setCategory('Test result file');
        // Add some data
        $this->spreadsheet->setActiveSheetIndex(0);
        $this->sheet = $this->spreadsheet->getActiveSheet();
        // Rename worksheet
        $this->spreadsheet->getActiveSheet()->setTitle('Sheet1');
    }

    //设置表头
    public function setHeader($title,$row = 2)
    {
        foreach ($title as $key => $item) $this->sheet->setCellValue(chr($key + 65) . ($row-1), $item);
        $this->row = $row; // 从第二行开始
        return $this;
    }
    //合并表头
    public function setMergeCells($cell)
    {
        foreach ($cell as $k => $v) $this->sheet->mergeCells($v);
        return $this;
    }
    /**
     * 设置某个单元格的下拉列表规则
     * @param [string] $cellPoint 单元格坐标. A1
     * @param [sting] $format 公式
     * @return void
     */
    public function setValidation($cellPoint, $format)
    {
        $validation = $this->sheet->getCell($cellPoint)->getDataValidation();
        $validation -> setType(DataValidation::TYPE_LIST);
        $validation -> setErrorStyle(DataValidation::STYLE_INFORMATION);
        $validation -> setAllowBlank(false);
        $validation -> setShowInputMessage(true);
        $validation -> setShowErrorMessage(true);
        $validation -> setShowDropDown(true);
        $validation -> setErrorTitle('输出错误');
        $validation -> setError('值不在列表中');
        $validation -> setPromptTitle('请选择');
        $validation -> setPrompt('请从列表中选择一个值');
        $validation -> setFormula1($format);
        // $validation->setFormula1('=major');//使用某个公式
        // $validation->setFormula1('"A1,A2,A3"');//可以直接写列表，用逗号分隔,最多255字符
        // $objValidation->setFormula1('=INDIRECT(F5)');//indirect间接的，以某个坐标为依托，联级
        return $this;
    }

    /**
     * 设置某个单元格的输入规则
     * @param [string] $cellPoint 单元格坐标. A1
     * @param [sting] type 输入类型 DataValidation::TYPE_LIST
     * @return void
     */
    public function setInputRule($cellPoint, $type)
    {
        $validation = $this->sheet -> getCell($cellPoint) -> getDataValidation();
        $validation -> setType($type);
        $validation -> setErrorStyle(DataValidation::STYLE_INFORMATION); //输错了的话显示的提示属于哪个级别
        $validation -> setAllowBlank(false);
        $validation -> setShowInputMessage(true);
        $validation -> setShowErrorMessage(true);
        $validation -> setErrorTitle('输入格式有误');
        return $this;
    }
    public function setExcelStyle($cell)
    {
        //样式设置 - 水平、垂直居中
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
            ],
        ];
        $this->sheet->getStyle($cell)->applyFromArray($styleArray);
        return $this;
        //样式设置 - 字体
        // $this->sheet->getStyle('A10:A24')->getFont()->setBold(true)->setName('Arial')->setSize(10);
    //     $this->sheet->getStyle('A18:A24')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED); //设置单元格A1的字体颜色

    //     //样式设置 - 边框
    //     $styleArray = [
    //         'borders' => [
    //             'outline' => [
    //                 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
    //                 'color' => ['argb' => '000000'],
    //             ],
    //             'inside' => [
    //                 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
    //             ]
    //         ],
    //     ];
    //     $this->sheet->getStyle('A1:F2')->applyFromArray($styleArray);

    //     //设置宽度
    //     foreach ($cell as $k => $v) {
    //         $this->sheet->getColumnDimension($v)->setWidth(13);
    //     }
    //     $this->sheet->getColumnDimension('A')->setWidth(20);

    //     $column_arr = array('B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M');
    //     $this->sheet->setCellValue('A10', '人员对比(正式工+毕业生)');
    //     foreach ($column_arr as $key => $val) {
    //         $mon = $key + 1;
    //         $this->sheet->setCellValue($column_arr[$key] . '10', $mon . '月');
    //     }
    //     $this->sheet->setCellValue('N10', '合计');
    //     $this->sheet->setCellValue('O10', '平均');

    //     $data_arr = array('B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O');
    //     $data_row_arr = array('11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24');
    //     $this->sheet->setCellValue('A11', '2019年人数(不含外包派遣)');
    //     foreach ($data_row_arr as $kkk => $vvv) {
    //         foreach ($data_arr as $kk => $vv) {
    //             $this->sheet->setCellValue($data_arr[$kk] . $vvv, 0);
    //         }
    //     }

    }
    //添加表内容
    public function addData($data)
    {
        foreach ($data as $item) {
            $dataCol = 'A';
            foreach ($item as $value) {
                // 单元格内容写入
                $this->sheet->setCellValue($dataCol . $this->row, $value);
                $dataCol++;
            }
            $this->row++;
        }
        return $this;
    }
    /**
     * [saveToLocal 保存到服务器本地]
     * @param  [type] $fileName [文件名]
     * @return [type]           [description]
     */
    public function saveToLocal(string $fileName):string
    {
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $this->spreadsheet->setActiveSheetIndex(0);
        $fileName = $fileName . '.xlsx';
        $Dir = 'files/excel';
        if (!file_exists('public/'.$Dir)) @mkdir('public/'.$Dir, 0644, true);
        $outFilename = BASE_PATH .'/public/'.$Dir.'/'.pathinfo($fileName,PATHINFO_BASENAME);
        $writer = IOFactory::createWriter($this->spreadsheet, 'Xlsx');
        $writer->save($outFilename);
        $this->spreadsheet->disconnectWorksheets();
        unset($this->spreadsheet);
        return $Dir.'/'.$fileName;
    }

    //直接从浏览器下载到本地，有问题，不使用
    // php://output 目前PhpSpreadsheet插件有问题，PhpSpreadsheet插件作者还在修复
    public function saveToBrowser($fileName)
    {
        $fileName = $fileName . '.xlsx';
        //xls='application/vnd.ms-excel'
        //xlsx='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'

        $writer = IOFactory::createWriter($this->spreadsheet, 'Xlsx');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $fileName . '"');
        header('Cache-Control: max-age=0');

        return $writer->save('php://output');
    }

    //保存临时文件在从浏览器自动下载到本地
    public function saveToBrowserByTmp($fileName)
    {
        $fileName = $fileName . '.xlsx';

        $writer = IOFactory::createWriter($this->spreadsheet, "Xlsx");
        //保存到服务器的临时文件下
        $writer->save("./tmp.xlsx");

        //将文件转字符串
        $content = file_get_contents('./tmp.xlsx');

        //删除临时文件
        unlink("./tmp.xlsx");

        $response = new Response();
        //xls='application/vnd.ms-excel'
        //xlsx='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        $contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';

        return $response->withHeader('content-description', 'File Transfer')
        ->withHeader('content-type', $contentType)
        ->withHeader('content-disposition', "attachment; filename={$fileName}")
        ->withHeader('content-transfer-encoding', 'binary')
        ->withHeader('pragma', 'public')
        ->withBody(new SwooleStream((string)$content));
    }
}